package org.hsieh.tr.explore.dbsource.impl;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.hsieh.tr.explore.annotation.Meta;
import org.hsieh.tr.explore.annotation.NullO;
import org.hsieh.tr.explore.dbsource.IDBObj;
import org.hsieh.tr.explore.dbsource.ISchemaEntity;
import org.hsieh.tr.explore.dbsource.ITableColumnEntity;
import org.hsieh.tr.explore.dbsource.ITableEntity;
import org.hsieh.tr.explore.exception.ExceptionHandler;
import org.hsieh.tr.explore.util.StringUtil;

@SuppressWarnings("unchecked")
public abstract class AbstractDBObj implements IDBObj {

    private transient DatabaseMetaData metaData;

    public DatabaseMetaData getMetaData() {
	return metaData;
    }

    public void setMetaData(DatabaseMetaData metaData) {
	this.metaData = metaData;
    }

    public static final String[] TYPES = new String[] { "TABLE", "VIEW" };

    /**
     * 1.TABLE_CAT
     * 
     * 2.TABLE_SCHEM
     * 
     * 3.TABLE_NAME
     * 
     * 4.TABLE_TYPE
     */
    public static List<ITableEntity> findTables(ISchemaEntity schema)
	    throws SQLException {
	List<ITableEntity> tables = new ArrayList<ITableEntity>();
	DatabaseMetaData metaData = schema.getMetaData();
	ResultSet rs = metaData.getTables(schema.getTableCatalog(), schema
		.getName(), null, TYPES);
	while (rs.next()) {
	    TableEntityImpl table = new TableEntityImpl();
	    table.setSchema(schema);
	    table.setMetaData(metaData);
	    table.setName(rs.getObject(3).toString());
	    table.setType(rs.getObject(4).toString());
	    tables.add(table);
	}
	return tables;
    }

    public List<ISchemaEntity> findSchemaEntities() throws SQLException {
	ResultSet schemas = getMetaData().getSchemas();
	List<ISchemaEntity> res = new ArrayList<ISchemaEntity>();
	while (schemas.next()) {
	    SchemaEntityImpl sche = new SchemaEntityImpl();
	    sche.setMetaData(getMetaData());
	    sche.setName(schemas.getString(1));
	    sche.setTableCatalog(getValue(schemas, 2).toString());
	    Object value = getValue(schemas, 3);
	    sche.setDefault(value instanceof NullO ? false : (Boolean) value);
	    res.add(sche);
	}
	if (res.isEmpty()) {
	    res.add(FakeSchema.create(getMetaData()));
	}
	return res;
    }

    /**
     * <ul>
     * <li>TABLE_CAT VARCHAR</li>
     * <li>TABLE_SCHEM VARCHAR</li>
     * <li>TABLE_NAME VARCHAR</li>
     * <li>COLUMN_NAME VARCHAR</li>
     * <li>DATA_TYPE SMALLINT</li>
     * <li>TYPE_NAME VARCHAR</li>
     * <li>COLUMN_SIZE INTEGER</li>
     * <li>BUFFER_LENGTH INTEGER</li>
     * <li>DECIMAL_DIGITS INTEGER</li>
     * <li>NUM_PREC_RADIX INTEGER</li>
     * <li>NULLABLE INTEGER</li>
     * <li>REMARKS VARCHAR</li>
     * <li>COLUMN_DEF VARCHAR</li>
     * <li>SQL_DATA_TYPE INTEGER</li>
     * <li>SQL_DATETIME_SUB INTEGER</li>
     * <li>CHAR_OCTET_LENGTH INTEGER</li>
     * <li>ORDINAL_POSITION INTEGER</li>
     * <li>IS_NULLABLE VARCHAR</li>
     * <li>SCOPE_CATLOG VARCHAR</li>
     * <li>
     * SCOPE_SCHEMA VARCHAR</li>
     * <li>SCOPE_TABLE VARCHAR</li>
     * <li>SOURCE_DATA_TYPE VARCHAR
     * </ul>
     * param table
     * 
     * @return
     * @throws SQLException
     */
    public List<ITableColumnEntity> findTableColumns(ITableEntity table)
	    throws SQLException {
	ResultSet rs = getMetaData().getColumns(
		table.getSchema().getTableCatalog(),
		table.getSchema().getName(), table.getName(), null);
	List<ITableColumnEntity> res = new ArrayList<ITableColumnEntity>();
	while (rs.next()) {
	    List<MetaColumn> metas = getAllColumns(rs.getMetaData());
	    if (metas.isEmpty()) {
		// We should do nothing here.
	    }
	    ITableColumnEntity column = new TableColumnImpl();
	    for (MetaColumn mc : metas) {
		ColumnProperty cp = new ColumnProperty(mc.getColumnName(), rs
			.getObject(mc.getColumnName()));
		column.addProperty(cp);
	    }
	    column.setColumnName(rs.getString(4));
	    res.add(column);
	}
	return res;
    }

    protected Object getValue(ResultSet rs, String field) {
	try {
	    return rs.getObject(field);
	} catch (Exception e) {
	}
	return NullO.get();
    }

    protected Object getValue(ResultSet rs, int field) {
	try {
	    return rs.getObject(field);
	} catch (Exception e) {
	}
	return NullO.get();
    }

    public static void get(ResultSet rs) throws SQLException {
	List<MetaColumn> all = getAllColumns(rs.getMetaData());
	if (all.isEmpty()) {
	    //
	}
	while (rs.next()) {
	    System.out.println("---------------------------------------");
	    for (MetaColumn meta : all) {
		System.out.println(meta.getColumnName() + " -> "
			+ rs.getObject(meta.getColumnName()));
	    }
	}
    }

    public static List<MetaColumn> getAllColumns(ResultSetMetaData rs)
	    throws SQLException {
	if (rs == null || rs.getColumnCount() <= 0) {
	    return Collections.EMPTY_LIST;
	}
	List<MetaColumn> res = new ArrayList<MetaColumn>();
	for (int i = 1; i <= rs.getColumnCount(); i++) {
	    MetaColumn column = new MetaColumn();
	    Class<? extends MetaColumn> klass = column.getClass();
	    Field[] fields = klass.getDeclaredFields();
	    Class<? extends ResultSetMetaData> rsClass = rs.getClass();
	    for (Field field : fields) {
		if (field.isAnnotationPresent(Meta.class)) {
		    Meta annotation = field.getAnnotation(Meta.class);
		    String metaColumnName = field.getName();
		    if (!StringUtil.isNullOrEmpty(annotation.columnName())) {
			metaColumnName = annotation.columnName();
		    }
		    boolean accessible = field.isAccessible();
		    field.setAccessible(true);
		    String methodName = "get"
			    + StringUtil.capitalize(metaColumnName);
		    try {
			Method method = rsClass
				.getMethod(methodName, int.class);
			if (method != null) {
			    Object value = method
				    .invoke(rs, new Object[] { i });
			    if (value != null) {
				field.set(column, value);
			    }
			}
		    } catch (Exception e) {
			System.out.println(methodName);
			ExceptionHandler.handle(e);
		    }
		    field.setAccessible(accessible);
		}
	    }
	    res.add(column);
	}
	return res;
    }

    public void refresh() {

    }
}
